package com.mopon.clean;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class QueryTask {

	/**
	 * 配置文件的路径为:config/
	 */
	public static final String CONFIG_PATH = System.getProperty("user.dir")
			+ File.separator + "config" + File.separator;

	/**
	 * 默认开始日期
	 */
	private final static String DEFAULT_START_DATE = "2017-01-01";
	
	/**
	 * SQL
	 */
	private final static String SQL = "SELECT * FROM (" +
			"SELECT CONCAT('/usr/local/mopon/card_data/', m.publ_id, '/', make_batch) paths FROM	T_CARD_MAKE_APPLY m, T_CARD_TYPE t WHERE m.CARD_TYPE_ID = t.CARD_TYPE_ID AND m.type = 1 " +
			"AND t.MEDIUM = 1 AND m.ADD_TIME > ? AND m.ADD_TIME < ? " + 
			" UNION " + 
			"SELECT CONCAT('/usr/local/mopon/card_data/', m.publ_id, '/', make_batch) paths FROM	T_CARD_MAKE_APPLY m, T_TICKET_TYPE t WHERE m.ticket_type_id = t.ticket_type_id AND m.type = 2 " +
			"AND t.MEDIUM = 1 AND m.ADD_TIME > ? AND m.ADD_TIME < ? " +
			") aa ORDER BY paths";

	
	public static void main(String[] args) throws Exception {
		// 从配置文件中读取到上次查询数据的日期
		Properties props = new Properties();
		props.load(new FileInputStream(CONFIG_PATH + "sysconfig.properties"));
		String startDate = props.getProperty("startDate", DEFAULT_START_DATE);

		String currentDate = DateUtil.getCurrentDate();
		boolean flag = processFiles(startDate, currentDate, props);
		if (flag) {
			// 将当前日期做为上次查询数据的日期
			props.put("startDate", currentDate);
			FileOutputStream fos = new FileOutputStream(CONFIG_PATH + "sysconfig.properties");
			props.store(fos, null);
			fos.close();
		}
	}
	
	private static boolean processFiles(String startDate, String endDate, Properties props) throws SQLException {
		boolean flag = true;
		Connection conn = null;
		
		try {
			Class.forName(props.getProperty("driverClass"));
			conn = DriverManager.getConnection(props.getProperty("url"), props.getProperty("username"), props.getProperty("password"));
			PreparedStatement pstmt = conn.prepareStatement(SQL) ;   
			pstmt.setString(1, startDate);
	        pstmt.setString(2, endDate);
	        pstmt.setString(3, startDate);
	        pstmt.setString(4, endDate);
	        ResultSet rs = pstmt.executeQuery();
	        List<String> paths = new ArrayList<String>();
	        while (rs.next()) {
	        	paths.add(rs.getString(1));
	        }
	        
	        rs.close();
	        pstmt.close();
	        conn.close();
	        
	        if (paths.isEmpty()) {
	        	flag = false;
	        } else {
	        	CleanFile.removeAndZipImages(paths);
	        }
		} catch (Exception e) {
			flag = false;
			e.printStackTrace();
		} 
		
		return flag;
	}

}
